# -*- coding: utf-8 -*-
"""
Created on Fri Sep 22 11:07:54 2017

@author: xuanlei

"""

import sqlalchemy as sa
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import time
from tqdm import tqdm
import datetime
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt  
import matplotlib as mpl
from locale import *
setlocale(LC_NUMERIC, 'English_US')
mpl.rcParams['axes.unicode_minus'] = False 
from pylab import *
mpl.rcParams['font.sans-serif'] = ['SimHei']  #作图显示中文
conn = sa.create_engine('postgresql://ytj@10.0.2.24:65432/powerdb').connect()

#===============================================================================
# 故障数据列表
#===============================================================================
err_table = [
    ["S1-09", "2014-05-20 08:10", "2014-05-21 18:00"],
    ["S2-22", "2014-09-10 08:10", "2014-09-10 18:10"],
    ["S1-55", "2014-09-11 08:10", "2014-09-12 18:10"],
    ["S2-51", "2014-10-31 08:10", "2014-10-31 18:00"],
    ["S1-03", "2015-01-11 08:10", "2015-01-13 23:10"],
    ["S2-22", "2015-01-27 16:00", "2015-03-06 17:55"],
    ["S2-23", "2015-01-16 16:10", "2015-03-02 17:10"],
    ["S1-42", "2015-02-16 17:45", "2015-03-07 16:10"],
    ["S2-60", "2015-03-14 10:46", "2015-03-30 17:30"],
    ["S1-17", "2015-04-12 13:55", "2015-04-21 15:20"],
    ["S1-64", "2015-03-24 11:00", "2015-05-16 16:32"],
    ["S1-50", "2015-04-25 13:35", "2015-06-21 18:00"],
    ["S1-67", "2015-02-25 06:47", "2015-06-30 17:05"],
    ["S1-58", "2015-11-26 17:56", "2016-03-18 19:04"],
    ["S2-59", "2016-01-30 08:10", "2016-03-25 14:27"],
    ["S1-07", "2016-04-07 00:47", "2016-04-23 17:25"],
    ["S1-66", "2016-08-31 16:13", "2016-09-09 16:09"],
    ["S1-59", "2016-10-07 07:47", "2016-10-23 15:58"],
    ["S1-27", "2016-11-15 20:09", "2016-11-29 22:07"],
    ["S2-42", "2016-11-15 13:11", "2016-12-04 20:30"],
    ["S2-40", "2016-12-22 17:13", "2017-01-05 12:32"],
    ["S1-38", "2017-02-28 09:04", "2017-02-28 16:00"],
    ["S1-14", "2017-04-23 09:04", "2017-04-23 16:00"],
    ["S1-63", "2017-06-17 15:16", "2017-06-26 18:16"]]

#===============================================================================
# 随机选取正常风机
#===============================================================================
run_table = [
        ["S1-52", "2016-05-20 08:10"],
        ["S1-21", "2017-03-20 08:10"],
        ["S1-13", "2017-05-20 08:10"],
        ["S2-05", "2017-08-22 08:10"],
        ["S2-32", "2017-04-20 08:10"],
        ["S2-10", "2017-11-20 08:10"],
        ["S2-08", "2016-06-11 08:10"],
        ["S1-02", "2017-01-11 08:10"],
        ["S1-11", "2016-07-20 08:10"],
        ["S1-53", "2016-12-20 08:10"],
        ["S2-35", "2016-05-22 08:10"],
        ["S2-33", "2016-09-20 08:10"],
        ["S2-17", "2016-11-20 08:10"],
        ["S2-55", "2016-06-11 08:10"],
        ["S1-50", "2017-05-20 08:10"],
        ["S1-29", "2017-03-20 08:10"],
        ["S1-43", "2017-05-20 08:10"],
        ["S2-03", "2016-08-22 08:10"],
        ["S2-62", "2017-04-20 08:10"],
        ["S2-11", "2016-11-20 08:10"],
        ["S2-10", "2016-06-11 08:10"]
        ]

feature = ['风机状态数字','风速m/s','发电机转速rpm','叶轮转速','叶尖压力','系统压力','风向角','偏航角度','齿轮油温度','齿轮箱轴承温度','环境温度','机舱温度','发电机前轴温度',
           '发电机后轴温度','发电机温度','A相电流','B相电流','C相电流','AB相电压','BC相电压','CA相电压','频率','有功功率kw','无功功率kvar','功率因素','电机发电量kwh','电机发电时间h',
           '消耗电量kwh','无功电度+kvarh','无功电度-kvarh','通电时间h','松闸时间h','故障时间h','系统OK时间h','维护时间h','风机可利用时间h','风可利用时间','待机时间','小风停机时间',
           '外部OK时间','定期检修时间','外部故障时间','标准运行小时','数据可用状态','时间']


feature_list = ['status_number','wind_speed','generator_speed','impellervelocity','tip_pressure','system_pressure','wind_direction','yaw_angle','gear_oil_temp',
                'gear_box_bearing_temp','environment_temperature','engine_room_temp','generator_front_shaft_temp','generator_back_shaft_temp','generator_temp',
                'A_phase_current','B_phase_current','C_phase_current','AB_phase_voltage','BC_phase_voltage','CA_phase_voltage','frequency','real_power',
                'wattless_power','power_factor','motor_generation','motor_time','power_consumption','KVARH+','KVARH-','voltaic_time','brake_release_time','fault_time',
                'system_OK_time','maintenance_time','availability_time','wind_availability_time','stand_by_time','small_wind_time','out_ok_time',
                'scheduled_maintenance_time','out_err_time','standard_run_hours','data_available_state','time']

f_list = ['wind_speed','generator_speed','tip_pressure','system_pressure','wind_direction','yaw_angle','gear_oil_temp',
                'gear_box_bearing_temp','environment_temperature','engine_room_temp','generator_front_shaft_temp','generator_back_shaft_temp','generator_temp',
                'A_phase_current','B_phase_current','C_phase_current','AB_phase_voltage','BC_phase_voltage','CA_phase_voltage','real_power']

table_range = [
    ["S1", 68, 134],
    ["S2", 136, 200],]

normal_sample = {
    "S1": [1, 5, 11, 25, 52],
    "S2": [1, 5, 11, 25, 52],
}

#===============================================================================
# load data
#===============================================================================
def load_data(wtno,end_time,days = 3,fb = 'up'):
    def load_df(schema, table_no, start_time, end_time):
        return pd.read_sql("""
                             SELECT * FROM "{0}"."tb_wt_{1}"
                             WHERE "WMAN_Tm" > '{2}' AND "WMAN_Tm" < '{3}'
                         """.format(schema, table_no, start_time, end_time), 
                         conn, parse_dates=["WMAN_Tm"])
    schema, no_str = wtno.split("-")
    table_no = int(no_str) + (67 if schema == "S1" else 134)
    if fb == 'up':
        end_times = datetime.datetime.strptime(end_time, "%Y-%m-%d %H:%M")
        start_time = end_times - datetime.timedelta(days)
        df = load_df(schema, table_no, start_time, end_time)
        pass
    elif fb == 'down':
        end_times = datetime.datetime.strptime(end_time, "%Y-%m-%d %H:%M")
        start_time = end_times + datetime.timedelta(days)
        df = load_df(schema, table_no, end_time,start_time)
    else:
        print('give right selection : up or down')
    return df


#===============================================================================
# 聚合数据：2分钟
#===============================================================================
def some_proc(df):
    def rolling(df_roll,para):
        if para == 'max':
            name_list = [item+'_max' for item in f_list]
            dfx = df_roll.rolling('120s').max()
            dfx.columns = name_list
            dfx.dropna(inplace = True)
            return dfx
        elif para == 'mean':
            name_list = [item+'_mean' for item in f_list]
            dfx = df_roll.rolling('120s').mean()
            dfx.columns = name_list
            dfx.dropna(inplace = True)
            return dfx
        elif para == 'min':
            name_list = [item+'_min' for item in f_list]
            dfx = df_roll.rolling('120s').min()
            dfx.columns = name_list
            dfx.dropna(inplace = True)
            return dfx
        elif para == 'var':
            name_list = [item+'_var' for item in f_list]
            dfx = df_roll.rolling('120s').var()
            dfx.columns = name_list
            dfx.dropna(inplace = True)
            dfx = dfx.fillna(method='pad')
            dfx = dfx.fillna(method='bfill')
            return dfx
        else:
            print('only: max,mean,min,var for now!')
        
    df.columns = feature_list
    df.index = df.time
    df.sort_index(inplace=True)
    df = df[f_list]
#    df.fillna(method='ffill', inplace=True)
#    df = df[df['real_power'].astype('float')>10.0]
#取值规约
    df.loc[:,'real_power'] = df.loc[:,'real_power']/10
    df.loc[:,'generator_speed'] = df.loc[:,'generator_speed']/100
    df.loc[:,'yaw_angle'] = df.loc[:,'yaw_angle']/10
    df.loc[:,'A_phase_current'] = df.loc[:,'A_phase_current']/10
    df.loc[:,'B_phase_current'] = df.loc[:,'B_phase_current']/10
    df.loc[:,'C_phase_current'] = df.loc[:,'C_phase_current']/10
    df.loc[:,'AB_phase_voltage'] = df.loc[:,'AB_phase_voltage']/10
    df.loc[:,'BC_phase_voltage'] = df.loc[:,'BC_phase_voltage']/10
    df.loc[:,'CA_phase_voltage'] = df.loc[:,'CA_phase_voltage']/10
    df.loc[:,'system_pressure'] = df.loc[:,'system_pressure']/10
    df.loc[:,'tip_pressure'] = df.loc[:,'tip_pressure']/10
    df.loc[:,'wind_direction'] = df.loc[:,'wind_direction']/10
    df_max = rolling(df,'max')
    df_mean = rolling(df,'mean')
    df_min = rolling(df,'min')
#    df_var = rolling(df,'var')
    temp = pd.concat([df_max,df_mean],axis = 1)
    df_roll1 = pd.concat([temp,df_min],axis = 1)
#    df_roll = pd.concat([df_roll1,df_var],axis = 1)
    df_roll = df_roll1.drop_duplicates()
#    df_rollp = df_roll.iloc[[i for i in np.arange(0,df_roll.shape[0],10)],:]
    df_rollp = df_roll[df_roll['real_power_min'].astype('float')>10.0]
    
#    df = df.apply(lambda x: (x - np.min(x)) / (np.max(x) - np.min(x))) 
    
    return df_rollp
    

#===============================================================================
# 加载故障数据
#===============================================================================
def get_err_data(err_table,days=3):
    print(">>>>>>>>>>>>>>>>>>>>>>>>>>")
    print('start load all error data...')     
    for err_no in tqdm(err_table):
        time.sleep(0.001)
        globals()[err_no[0].lower().replace('-','_')+'_dfef'] =some_proc(load_data(err_no[0],err_no[1],days,fb = 'up'))
        globals()[err_no[0].lower().replace('-','_')+'_dfeb'] =some_proc(load_data(err_no[0],err_no[2],days,fb = 'down'))
        globals()[err_no[0].lower().replace('-','_')+'_dfer'] = globals()[err_no[0].lower().replace('-','_')+'_dfef'].append(globals()[err_no[0].lower().replace('-','_')+'_dfeb'])
    print('err load Done')     
    print("<<<<<<<<<<<<<<<<<<<<<<<<<<\n")  
#===============================================================================
# 加载正常风机数据
#===============================================================================
def get_run_data(run_table,days = 3):
    print(">>>>>>>>>>>>>>>>>>>>>>>>>>")
    print('start load sample run data...')     
    for run_no in tqdm(run_table):
        time.sleep(0.001)
        globals()[run_no[0].lower().replace('-','_')+'_dfrr'] =some_proc(load_data(run_no[0],run_no[1],days,fb = 'up'))
    print('run load Done')     
    print("<<<<<<<<<<<<<<<<<<<<<<<<<<\n")
#===============================================================================
# 贴标签
#===============================================================================
def labelling(l_r=0):
    result_run = []
    result_err_e = []
    result_err_r = []
    def label_on(df,t,l_r=0):
        '''
        t like this：
        ['2017-03-01 00:00:00']
        '''
        t = datetime.datetime.strptime(t, '%Y-%m-%d %H:%M')
#        label = [int((t-l).total_seconds()/18) for l in df.index] # label间隔为10
        label = [int((t-l).total_seconds()/18) for l in df.index]
        for index, value in enumerate(label):
#            if value > 48000:#10天（15s/16s/17s级数据）
            if value > 48000:#10        5天:7200（60s级数据）
#                label[index]=48000
                label[index]=48000
        df['label'] = label
#        dfl = df[df.label<48000]
        dfl = df[df.label<48000] 
        dff = dfl[dfl.label>0]
        dfb = dfl[dfl.label<0]
        if l_r:
#            dfb['label'] = 48000
            dfb['label'] = 48000
            return dff,dfb
        elif l_r ==0:
            dff['label'] = 1 
            dfb['label'] = 0
            return dff,dfb
    print(">>>>>>>>>>>>>>>>>>>>>>>>>>")
    print('start giving label on data...')
    if l_r:
        for err in err_table:
            result_err_e.append(label_on(globals()[err[0].lower().replace('-','_')+'_dfer'],err[1],l_r=1)[0])
            result_err_r.append(label_on(globals()[err[0].lower().replace('-','_')+'_dfer'],err[2],l_r=1)[1])
        for run in run_table:
            result_run.append(label_on(globals()[run[0].lower().replace('-','_')+'_dfrr'],run[1],l_r=1)[0])
        pass
    elif l_r==0:
        for err in err_table:
            result_err_e.append(label_on(globals()[err[0].lower().replace('-','_')+'_dfer'],err[1])[0])
            result_err_r.append(label_on(globals()[err[0].lower().replace('-','_')+'_dfer'],err[2])[1])
        for run in run_table:
            temp_df = label_on(globals()[run[0].lower().replace('-','_')+'_dfrr'],run[1])[0]
            temp_df['label'] = 0
            result_run.append(temp_df)
            pass
        pass

    print('giving label on data  Done')
    print("<<<<<<<<<<<<<<<<<<<<<<<<<<\n")
    return result_run,result_err_e,result_err_r
        
    
#===============================================================================
# 特征选择，绘图
#===============================================================================
def pplot(feature):
    result_run[0].loc[:,feature].plot(subplots=1,use_index = 0,label='S1-52号正常风机',color='g')
    result_run[1].loc[:,feature].plot(subplots=1,use_index = 0,label='S1-21号正常风机',color='g')
    result_run[2].loc[:,feature].plot(subplots=1,use_index = 0,label='S1-13号正常风机',color='g')
    result_err_e[0].loc[:,feature].plot(subplots=1,use_index = 0,label='S1-09号故障风机',color='r')
    result_err_e[6].loc[:,feature].plot(subplots=1,use_index = 0,label='S2-23号故障风机',color='r')
    result_err_e[2].loc[:,feature].plot(subplots=1,use_index = 0,label='S2-22号故障风机',color='r')
    result_err_e[3].loc[:,feature].plot(subplots=1,use_index = 0,label='S1-55号故障风机',color='r')
    plt.legend(loc=1)
    plt.title(feature+'：变化趋势对比')
    plt.xlabel('时间区间（连续的数据条数）')
    plt.ylabel('对应值域')
#    plt.savefig(feature+'：变化趋势对比', dpi=900)

#===============================================================================
# 入口
#===============================================================================
def get_start():
    '''
    return :
    result_run(normal wtno)
    result_err_e(error data of error wtno)
    result_err_r(normal data of error wtno)
    
    '''
    get_err_data(err_table,30)
    get_run_data(run_table,30)
    result_run,result_err_e,result_err_r  = labelling(l_r=0)
